package com.zhisheng.day16_echarts.utils;

import com.zhisheng.day16_echarts.entity.StudentScore;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ExcelToStudentScoreUtil {

    public static List<StudentScore> convertExcelToStudentScore(InputStream inputStream) {
        List<StudentScore> studentScoreList = new ArrayList<>();
        try (
             Workbook workbook = new XSSFWorkbook(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                StudentScore studentScore = new StudentScore();
                studentScore.setName(getCellValue(row, 0));
                studentScore.setFeiShu(getCellValue(row, 2));
                studentScore.setHomeworkType(getCellValue(row, 3));
                studentScore.setHomeworkUrl(getCellValue(row, 4));
                studentScore.setHomeworkFile(getCellValue(row, 5));
                studentScore.setSubmitTime(getCellDateValue(row, 6));
                studentScore.setScore(getCellDoubleValue(row, 7));
                studentScore.setLevel(getCellValue(row, 8));
                studentScore.setDescript(getCellValue(row, 9));
                studentScore.setTeacherName(getCellValue(row, 10));
                studentScore.setSeatIndex(getCellValue(row, 11));
                studentScoreList.add(studentScore);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return studentScoreList;
    }

    private static String getCellValue(Row row, int cellIndex) {
        Cell cell = row.getCell(cellIndex);
        if (cell == null) {
            return null;
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm").format(cell.getDateCellValue());
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return null;
        }
    }

    private static Date getCellDateValue(Row row, int cellIndex) {
        Cell cell = row.getCell(cellIndex);
        if (cell == null || cell.getCellType() != CellType.NUMERIC ||!DateUtil.isCellDateFormatted(cell)) {
            return null;
        }
        return cell.getDateCellValue();
    }

    private static Double getCellDoubleValue(Row row, int cellIndex) {
        Cell cell = row.getCell(cellIndex);
        if (cell == null || cell.getCellType() != CellType.NUMERIC) {
            return null;
        }
        return cell.getNumericCellValue();
    }
}